package cn.edu.zjicm.wordsnet_d.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.thrift.TException;

import android.content.Context;
import android.database.Cursor;

import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;

import android.util.Log;

import cn.edu.zjicm.wordsnet_d.R;
import cn.edu.zjicm.wordsnet_d.activity.ExamRunActivity;
import cn.edu.zjicm.wordsnet_d.data.Affix;
import cn.edu.zjicm.wordsnet_d.data.MobclickAgentData;
import cn.edu.zjicm.wordsnet_d.data.OneWord;
import cn.edu.zjicm.wordsnet_d.data.RelationShip;
import cn.edu.zjicm.wordsnet_d.data.Sentence;
import cn.edu.zjicm.wordsnet_d.data.SortByLevelMin;
import cn.edu.zjicm.wordsnet_d.data.StaticData;
import cn.edu.zjicm.wordsnet_d.data.StudyPlan;
import cn.edu.zjicm.wordsnet_d.data.SynData;
import cn.edu.zjicm.wordsnet_d.data.WordLog;
import cn.edu.zjicm.wordsnet_d.data.searchDate;
import cn.edu.zjicm.wordsnet_d.datapersistent.AppPreference;
import cn.edu.zjicm.wordsnet_d.pay.Order;
import cn.edu.zjicm.wordsnet_d.pay.Product;
import cn.edu.zjicm.wordsnet_d.synchronize.StringZip;
import cn.edu.zjicm.wordsnet_d.synchronize.ThriftSerialization;
import cn.edu.zjicm.wordsnet_d.util.DateTimeUtils;
import cn.edu.zjicm.wordsnet_d.util.LogUtil;
import cn.edu.zjicm.wordsnet_d.util.StringUtil;

public class WordFactory {
    public static final String TABLE_WORDS = "words";
    public static final String TABLE_WORDS_LOG = "words_log";
    public static final String TABLE_WORD_BOOK = "word_book";
    public static final String TABLE_WORDS_LEVELS = "words_levels";
    public static final String TABLE_COLLOC = "colloc";
    public static final String TABLE_EXA_SENTENCE = "exasentence";
    public static final String TABLE_AFFIX_INFO = "affix_info";
    public static final String TABLE_AFFIX_NODE = "affix_node";
    public static final String TABLE_PUNCH_OUT_LOG = "punch_out_log";
    public static final String TABLE_SAME_SHAPE = "sameshape";
    public static final String TABLE_ORDER_LOG = "order_log";
    public static final String TABLE_PRODUCT = "product";
    public static final String TABLE_WEALTH = "wealth";

    public static final double MAGICRATE = 0.618;

    private static SQLiteDatabase db;
    public static WordFactory instance = null;
    private Context context;

    private void openDB(Context context) {
        if (db == null || !db.isOpen()) {
            try {
                SQLiteDatabase.loadLibs(context);
                db = (SQLiteDatabase) DatabaseKey.getReadableDatabase(new DatabaseHelper(context));
                LogUtil.addLog("dbversion:" + db.getVersion());
            } catch (Throwable t) {
                t.printStackTrace();
                Log.e("error", "exit");
                LogUtil.addLog("opendb fail");
                System.exit(0);
            }
        }
    }

    private WordFactory(Context context) {
        openDB(context);
        if (instance == null) {
            instance = this;
        }
        this.context = context;
    }

    public synchronized static WordFactory getInstance(Context context) {
        if (instance == null) {
            instance = new WordFactory(context);
            // 如果表格words不存在(新安装用户)或参数为第一次使用新版(数据库升级用户)，则保存用户信息，复制数据库，回复用户数据
            if (!instance.tabbleIsExist(WordFactory.TABLE_WORDS) || AppPreference.getIsFirstUseThisVersion(context)) {
                db.close();
                instance = null;
                copyDB(context);
                instance = new WordFactory(context);
                instance.createIndexs();
                instance.importTableWordsLog(); // 恢复用户单词学习记录表
                instance.importTablePunchOutLog(); // 恢复用户打卡记录表
                instance.importTableZMD(); // 恢复用户财富表
                instance.importTableOrderLog(); // 恢复用户购买表
                countNewWordsFmDegree(context);
                AppPreference.setIsFirstUseThisVersion(context, false);
            }
        }
        return instance;
    }

    // 计算单词统计个数
    public static void countNewWordsFmDegree(Context context) {
        int lastStudyDate = AppPreference.getStudyDate(context);
        if (lastStudyDate == -1)
            return;
        AppPreference.setNewWordsFmDegree(context, instance.getUnLearnedWordCount(), 0);
        AppPreference.setNewWordsFmDegree(context, instance.getWordsFmDegree(1), 1);
        AppPreference.setNewWordsFmDegree(context, instance.getWordsFmDegree(2), 2);
        AppPreference.setNewWordsFmDegree(context, instance.getWordsFmDegree(3), 3);
        AppPreference.setNewWordsFmDegree(context, instance.getWordsFmDegree(4), 4);
        AppPreference.setNewWordsFmDegree(context, instance.getFamiliarWordCount(), 5);
        AppPreference.setNewWordsFmDegree(context, instance.getTooEasyWordCount(), 6);

        for (int i = 0; i < 7; i++)
            AppPreference.setWordsFmDegree(context, AppPreference.getNewWordsFmDegree(context, i), i);
    }

    public static void copyDB(Context context) {
        File file = new File(DatabaseHelper.DB_PATH + DatabaseHelper.DATABASE_NAME);
        try {
            FileOutputStream os = new FileOutputStream(file);
            InputStream in = context.getResources().openRawResource(R.raw.db_20150602_has_key);
            byte[] buffer = new byte[4096];
            while (in.available() > 0) {
                int i = in.read(buffer);
                if (i > 0)
                    os.write(buffer, 0, i);
                os.flush();
            }
            in.close();
            os.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库版本号
    public int getVersion() {
        instance = null;
        return db.getVersion();
    }

    /**
     * 判断某张表是否存在
     *
     * @param tabName 表名
     * @return
     */
    public boolean tabbleIsExist(String tableName) {
        boolean result = false;
        if (tableName == null) {
            return false;
        }

        String sql = "select count(1) as c from Sqlite_master where type ='table' and name ='" + tableName.trim() + "' ";
        int count = getInt(sql);
        if (count > 0) {
            result = true;
        } else {
            // db.close();
            // instance = null;
        }
        return result;
    }

    public int getInt(String sql) {
        Cursor c = db.rawQuery(sql, null);
        int ret = 0;
        if (c.moveToNext()) {
            ret = c.getInt(0);
        }
        c.close();

        return ret;
    }

    public void dbexe(String sql) {
        // 暂时去了try-catch
        // try {
        db.execSQL(sql);
        // } catch (Exception e) {
        // e.printStackTrace();
        // }
    }

    // 获取单词表中所有单词的个数
    public int getTotalCount() {
        String sql = "select count(1) from " + TABLE_WORDS;
        return getInt(sql);
    }

    // 获取单词列表
    public ArrayList<OneWord> getWordsList(String sql) {
        Cursor c;
        ArrayList<OneWord> words = new ArrayList<OneWord>();

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            OneWord word = new OneWord(c);
            // word.setGroupBy(OneWord.GROUP_BY_UNIT);
            words.add(word);
        }
        c.close();
        return words;
    }

    // 获取单词列表
    public List<OneWord> getWordsList(String sql, boolean onlyWordInfo) {
        Cursor c;
        List<OneWord> words = new ArrayList<OneWord>();

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            OneWord word = new OneWord(c, onlyWordInfo);
            // word.setGroupBy(OneWord.GROUP_BY_UNIT);
            words.add(word);
        }
        c.close();
        return words;
    }

    // 获取需要巩固的熟词列表
    public List<OneWord> getConsolidateWordsLimitTen() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " order by next_test_time ASC"
                + " limit 10";
        return getWordsList(sql);
    }

    // 获取需要巩固的熟词列表
    public List<OneWord> getConsolidateWords() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " order by next_test_time ASC";
        return getWordsList(sql);
    }

    // 获取需要巩固的熟词列表
    public List<OneWord> getConsolidateWordsLimit(int fromIndex, int toIndex) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " order by next_test_time ASC"
                + " limit " + fromIndex + "," + toIndex;
        return getWordsList(sql);
    }

    public OneWord getOneWordOfWhere(String where) {
        // long start1 = System.currentTimeMillis();

        String sql = "select * from " + TABLE_WORDS + " left join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + where;
        Cursor c = db.rawQuery(sql, null);
        OneWord word = null;
        if (c.moveToNext())
            word = new OneWord(c);
        c.close();

        // LogUtil.e("getOneWordOfWhere():" + word.getLemma(),
        // System.currentTimeMillis() - start1 + "");
        return word;
    }

    public OneWord getOneWordOfWhere1(String where) {
        // long start1 = System.currentTimeMillis();

        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + where;
        Cursor c = db.rawQuery(sql, null);
        OneWord word = null;
        if (c.moveToNext())
            word = new OneWord(c);
        c.close();

        // LogUtil.e("getOneWordOfWhere():" + word.getLemma(),
        // System.currentTimeMillis() - start1 + "");
        return word;
    }

    public OneWord getOneWordById(int id) {
        return getOneWordOfWhere("index_word_id=" + id);
    }

    // 获取生词列表
    public List<OneWord> getUnFamiliarWords() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and sense_degree_fm>0 "
                + " order by time_set DESC";
        return getWordsList(sql);
    }

    // 获取生词列表
    public List<OneWord> getUnFamiliarWordsLimit(int fromIndex, int toIndex) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and sense_degree_fm>0 "
                + " order by time_set DESC" + " limit " + fromIndex + "," + toIndex;
        return getWordsList(sql);
    }

    // 获取生词列表中每个sense_degree_fm等级的个数
    public void getUnFamiliarWords1() {
        String sql = "select sense_degree_fm,count(1) from " + TABLE_WORDS_LOG + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR
                + " and sense_degree_fm>0 " + " group by sense_degree_fm";
        Cursor c;
        int sum = 0;
        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            sum += c.getInt(1);
        }
        Log.e("sum", sum + "");
        c.close();

        sql = "delete from " + TABLE_WORDS_LOG + " where index_word_id=1115820"; // 清除id为1115820的单词(新数据库中无此单词)
        dbexe(sql);
    }

    // 将生词改为未学
    public void updateUnFamiliarWordsToUnlearn() {
        String sql = "delete from " + TABLE_WORDS_LOG + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR;
        dbexe(sql);
    }

    // 修复sense_degree_fm不正常的值(/10)
    public void updateUnFamiliarWordsDegree() {
        String sql = "update " + TABLE_WORDS_LOG + " set sense_degree_fm=sense_degree_fm/10" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and sense_degree_fm>=100";
        dbexe(sql);
    }

    // 修复sense_degree_fm不正常的值(/10)
    public void updateDifficulty(int diff, int wordId) {
        String sql = "update " + TABLE_WORDS_LOG + " set difficulty= " + diff + " where index_word_id=" + wordId;
        dbexe(sql);
    }

    // 获取熟词列表
    public List<OneWord> getFamiliarWords() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm<"
                + OneWord.SENSE_DEGREE_TOO_EASY + " order by last_test_time DESC";
        return getWordsList(sql);
    }

    // 获取熟词列表
    public List<OneWord> getFamiliarWordsLimit(int fromIndex, int toIndex) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm<"
                + OneWord.SENSE_DEGREE_TOO_EASY + " order by last_test_time DESC" + " limit " + fromIndex + "," + toIndex;
        return getWordsList(sql);
    }

    // 获取难词列表
    public List<OneWord> getDifficultyWordsLimit(int fromIndex, int toIndex) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where difficulty=" + 1 + " order by last_test_time DESC" + " limit " + fromIndex
                + "," + toIndex;
        return getWordsList(sql);
    }

    // 加量学习中的熟词词义回想，获取熟词列表
    public List<OneWord> getFamiliarWordsForReview() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm<"
                + OneWord.SENSE_DEGREE_TOO_EASY + " order by time_lock ASC"
                // + " order by last_test_time ASC";
                + " limit 10";
        return getWordsList(sql);
    }

    // 加量学习中的熟词词义回想，获取熟词列表
    public List<OneWord> getFamiliarWordsForReview1() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm<"
                + OneWord.SENSE_DEGREE_TOO_EASY + " order by time_lock ASC"
                // + " order by last_test_time ASC";
                + " limit 10," + (getFamiliarWordCount() - 10);
        return getWordsList(sql);
    }

    // 获取太简单列表
    public List<OneWord> getTooEasyWords() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm>="
                + OneWord.SENSE_DEGREE_TOO_EASY + " order by time_set DESC";
        return getWordsList(sql);
    }

    // 获取太简单列表
    public List<OneWord> getTooEasyWordsLimit(int fromIndex, int toIndex) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm>="
                + OneWord.SENSE_DEGREE_TOO_EASY + " order by time_set DESC" + " limit " + fromIndex + "," + toIndex;
        return getWordsList(sql);
    }

    // 获取熟悉度为1/2/3/4的单词个数
    public int getWordsFmDegree(int fmDegree) {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and ";
        if (fmDegree == 1)
            sql += "(sense_degree_fm/10=1 or sense_degree_fm/10=2)";
        else if (fmDegree == 2)
            sql += "(sense_degree_fm/10=3)";
        else if (fmDegree == 3)
            sql += "(sense_degree_fm/10=4)";
        else if (fmDegree == 4)
            sql += "(sense_degree_fm/10=5)";
        return getInt(sql);
    }

    // 计算当前学习计划中的单词书的未学单词个数
    public int getUnLearnedWordCount() {
        int allUnlearnedWordCount = 0;
        if (StudyPlan.isLearningWordBook(context)) {
            allUnlearnedWordCount += getUnLearnedWordCount(AppPreference.getWordBookIndex(context));
        }
        if (StudyPlan.isLearningPhraseBook(context)) {
            allUnlearnedWordCount += getUnLearnedWordCount(AppPreference.getPhraseBookIndex(context));
        }
        return allUnlearnedWordCount;
    }

    // 获得未学单词个数
    public int getUnLearnedWordCount(int bookIndex) {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " inner join " + TABLE_WORDS_LEVELS + " on " + TABLE_WORDS_LOG + ".index_word_id=" + TABLE_WORDS_LEVELS + ".index_word_id" + " and "
                + TABLE_WORDS_LEVELS + ".book_id=" + AppPreference.Book.BOOK_ID[bookIndex] + " where sense_degree_fm>" + OneWord.DEGREE_UNLEARN;
        return getWordCountInBook(bookIndex) - getInt(sql); // 单词书中的单词总数-单词书中的已学单词个数
    }

    // 获得某本单词书中的单词总数
    public int getWordCountInBook(int bookIndex) {
        String sql = "select word_count from " + TABLE_WORD_BOOK + " where book_id=" + AppPreference.Book.BOOK_ID[bookIndex];
        return getInt(sql);
    }

    // 计算待学队列中的单词个数
    public int getPlanToLearnedWordCount() {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where sense_degree_fm=" + OneWord.SENSE_DEGREE_UNLEARN + " and degree_fm="
                + OneWord.DEGREE_UNFAMILIAR;
        return getInt(sql);
    }

    // 生词个数
    public int getUnFamiliarWordCount() {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where sense_degree_fm>" + OneWord.SENSE_DEGREE_UNLEARN + " and degree_fm="
                + OneWord.DEGREE_UNFAMILIAR;
        return getInt(sql);
    }

    // 熟词个数
    public int getFamiliarWordCount() {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm<"
                + OneWord.SENSE_DEGREE_TOO_EASY;
        return getInt(sql);
    }

    // 太简单单词个数
    public int getTooEasyWordCount() {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm>="
                + OneWord.SENSE_DEGREE_TOO_EASY;
        return getInt(sql);
    }

    // 用户所学单词总数
    public int getStudyWordCount() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG;
        return getInt(sql);
    }

    // 用户所学单词总数
    public int getStudyWordCount1() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " where sense_degree_fm>0";
        return getInt(sql);
    }

    // 获取今天必须要巩固的单词数
    public int getConsolidateNumsLatestToday() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/"
                + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " and next_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset());
        return getInt(sql);
    }

    // 获取3天内必须要巩固的单词数
    public int getConsolidateNumsLatestIn3Days() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/"
                + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " and next_test_time/" + DateTimeUtils.MINUTES_OF_DAY + ">="
                + (DateTimeUtils.getDateWithZoneRawOffset() + 1) + " and next_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<=" + (DateTimeUtils.getDateWithZoneRawOffset() + 3);
        return getInt(sql);
    }

    // 获取7天内必须要巩固的单词数
    public int getConsolidateNumsLatestIn7Days() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/"
                + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " and next_test_time/" + DateTimeUtils.MINUTES_OF_DAY + ">"
                + (DateTimeUtils.getDateWithZoneRawOffset() + 3) + " and next_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<=" + (DateTimeUtils.getDateWithZoneRawOffset() + 7);
        return getInt(sql);
    }

    // 获取7天后要巩固的单词数
    public int getConsolidateNumsLatestAfter7Days() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/"
                + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3) + " and next_test_time/" + DateTimeUtils.MINUTES_OF_DAY + ">"
                + (DateTimeUtils.getDateWithZoneRawOffset() + 7);
        return getInt(sql);
    }

    // 获取需要巩固的熟词列表
    public int getConsolidateWordsNum() {
        String sql = "select count(1) from " + TABLE_WORDS_LOG + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR + " and last_test_time/"
                + DateTimeUtils.MINUTES_OF_DAY + "<" + (DateTimeUtils.getDateWithZoneRawOffset() - 3);
        return getInt(sql);
    }

    // 45分钟、12小时、1.25天（30小时）、3天、7天、15天
    // int wordReviewTime[] = {0, 45, 12*60, 30*60, 3*24*60, 7*24*60, 15*24*60};
    // 0、2小时、16小时(1天)、16小时(1天)、40小时(2天)、96小时(4天)、168小时(7天)
    // public static int wordReviewTime[] = { 0, 0, 2 * 60, 16 * 60, 16 * 60, 40
    // * 60, 4 * 24 * 60, 7 * 24 * 60 };
    public static int wordReviewTime[] = {0, 0, 16 * 60, 40 * 60, 4 * 24 * 60, 7 * 24 * 60};

    public List<OneWord> getWordsForTestOfDegree(int degree, int mode, boolean newWordPriority) {
        int count = 0;
        if (mode == ExamRunActivity.STUDY_MODE)
            count = getNumberOfWordsForTestOfDegree(degree);
        else
            count = getRealNumberOfWordsForTestOfDegree(degree);
        String sql = null;
        if (count > 10) {
            sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + "degree_fm=" + degree + " and " + "("
                    + "sense_degree_fm/10=0 or sense_degree_fm/10=1" + " or " + "(sense_degree_fm/10=2" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 1) + ")" + " or " + "(sense_degree_fm/10=3" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 2) + ")" + " or " + "(sense_degree_fm/10=4" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 4) + ")" + " or " + "(sense_degree_fm/10=5" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 7) + ")" + ")" + " order by sense_degree_fm asc,last_test_time asc"; // 按熟悉度降序，学习时间升序排序;
            // 20141113改为按熟悉度升序
            if (!newWordPriority)
                sql = sql + " limit " + (count - 10) + "," + 10;
            else
                sql = sql + " limit 10";
            count = 10;
        } else {
            sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + "degree_fm=" + degree + " and " + "("
                    + "sense_degree_fm/10=0 or sense_degree_fm/10=1" + " or " + "(sense_degree_fm/10=2" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 1) + ")" + " or " + "(sense_degree_fm/10=3" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 2) + ")" + " or " + "(sense_degree_fm/10=4" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 4) + ")" + " or " + "(sense_degree_fm/10=5" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                    + (DateTimeUtils.getDateWithZoneRawOffset() - 7) + ")" + ")" + " order by sense_degree_fm asc,last_test_time asc" // 按熟悉度降序，学习时间升序排序;
                    // 20141113改为按熟悉度升序
                    + " limit " + count;
        }
        return getWordsList(sql);
    }

    public List<OneWord> getWordsForTestOfDegree1(int degree, int mode, boolean newWordPriority) {
        int count = 0;
        if (mode == ExamRunActivity.STUDY_MODE)
            count = getNumberOfWordsForTestOfDegree(degree);
        else
            count = getRealNumberOfWordsForTestOfDegree(degree);
        if (count <= 10)
            return new ArrayList<OneWord>();
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + "degree_fm=" + degree + " and " + "("
                + "sense_degree_fm/10=0 or sense_degree_fm/10=1" + " or " + "(sense_degree_fm/10=2" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 1) + ")" + " or " + "(sense_degree_fm/10=3" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 2) + ")" + " or " + "(sense_degree_fm/10=4" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 4) + ")" + " or " + "(sense_degree_fm/10=5" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 7) + ")" + ")" + " order by sense_degree_fm asc,last_test_time asc"; // 按熟悉度降序，学习时间升序排序;
        // 20141113改为按熟悉度升序
        if (!newWordPriority)
            sql = sql + " limit " + (count - 10);
        else
            sql = sql + " limit 10," + (count - 10);
        return getWordsList(sql);
    }

    public int getRealNumberOfWordsForTestOfDegree(int degree) {
        // String sql = "select count(1) from " + TABLE_WORDS_LOG
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + "degree_fm=" + degree + " and " + "("
                + "sense_degree_fm/10=0 or sense_degree_fm/10=1" + " or " + "(sense_degree_fm/10=2" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 1) + ")" + " or " + "(sense_degree_fm/10=3" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 2) + ")" + " or " + "(sense_degree_fm/10=4" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 4) + ")" + " or " + "(sense_degree_fm/10=5" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 7) + ")" + ")";
        return getInt(sql);
    }

    public int getNumberOfWordsForTestOfDegree(int degree) {
        int newWordCount = getNumberOfWordListNotStudyOfDegree(degree); // 新学单词个数
        LogUtil.d("newWordCount=" + newWordCount);
        int reviewWordCount = AppPreference.getTodayShouldReviewWordCount(context) - AppPreference.getTodayReviewWordCount(context); // 复习单词个数
        if (reviewWordCount < 0)
            reviewWordCount = 0;
        return newWordCount + reviewWordCount;
    }

    public int getNumberOfWordsTodayShouldReview(int degree) {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where " + "degree_fm=" + degree + " and " + "(" + "(sense_degree_fm/10=2"
                + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<=" + (DateTimeUtils.getDateWithZoneRawOffset() - 1) + ")" + " or " + "(sense_degree_fm/10=3" + " and last_test_time/"
                + DateTimeUtils.MINUTES_OF_DAY + "<=" + (DateTimeUtils.getDateWithZoneRawOffset() - 2) + ")" + " or " + "(sense_degree_fm/10=4" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY
                + "<=" + (DateTimeUtils.getDateWithZoneRawOffset() - 4) + ")" + " or " + "(sense_degree_fm/10=5" + " and last_test_time/" + DateTimeUtils.MINUTES_OF_DAY + "<="
                + (DateTimeUtils.getDateWithZoneRawOffset() - 7) + ")" + ")";
        int reviewWordCount = getInt(sql);
        int n6 = get6n();
        if (reviewWordCount > n6)
            reviewWordCount = n6;
        return reviewWordCount;
    }

    public int get6n() {
        int tooEasyWordCount = getTooEasyWordCount();
        int studyWordCount = getStudyWordCount1();

        double d = 1.0 * (studyWordCount - tooEasyWordCount) / studyWordCount;

        if (d < 0.5)
            d = 0.5;

        int newWordCountPerDay = DateTimeUtils.getWordBookNumPerDayForPlan(context, false) + DateTimeUtils.getPhraseBookNumPerDayForPlan(context, false);
        if (newWordCountPerDay == 0)
            newWordCountPerDay = DateTimeUtils.getWordBookNumPerDayForPlan(context, true) + DateTimeUtils.getPhraseBookNumPerDayForPlan(context, true);
        return (int) (6 * d * newWordCountPerDay);
    }

    // 刚加入生词本的未学习单词数
    public int getNumberOfWordListNotStudyOfDegree(int degree) {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where" + " degree_fm=" + degree + " and sense_degree_fm<"
                + OneWord.SENSE_DEGREE_UNFAMILIAR1;
        return getInt(sql);
    }

    // 先取 前天加入生词本的单词 去复习，然后是今天加入生词本的单词，然后是生词本中最久未复习的单词。
    public List<OneWord> getWordForLock() {
        List<OneWord> list = new ArrayList<OneWord>();

        int num = AppPreference.getLockScreenWordsNum(context);
        // 从生词本取
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and sense_degree_fm>"
                + OneWord.SENSE_DEGREE_UNLEARN + " order by time_lock asc" + " limit " + num;
        list.addAll(getWordsList(sql));

        // 从熟词本取
        if (list.size() < num) {
            sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                    + OneWord.SENSE_DEGREE_FAMILIAR + " order by time_lock asc" + " limit " + (num - list.size());
            list.addAll(getWordsList(sql));
        }

        // 从太简单取
        if (list.size() < num) {
            sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                    + OneWord.SENSE_DEGREE_TOO_EASY + " order by time_lock asc" + " limit " + (num - list.size());
            list.addAll(getWordsList(sql));
        }

        // 从未学单词取
        if (list.size() < num) {
            int total = getTotalCount() - (num - list.size());
            sql = "select * from " + TABLE_WORDS + " limit " + rand(total) + "," + (num - list.size());
            list.addAll(getWordsList(sql, true));
        }

        return list;
    }

    // 得到新学单词或最近忘记的单词,用学习时间排序
    public List<OneWord> getNewWordOrLastForgetWords() {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR
                + " and (sense_degree_fm/10=1 or sense_degree_fm/10=2" + " or time_forget > " + (DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() - 24 * 60) + ")" + " order by time_lock ASC";
        return getWordsList(sql);
    }

    // 得到新学单词或最近忘记的单词，用听音熟悉度排序
    public List<OneWord> getNewWordOrLastForgetWordsForHuanboLimit(int limitNums) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR
                + " and (sense_degree_fm/10=1 or sense_degree_fm/10=2" + " or time_forget > " + (DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() - 24 * 60) + ")"
                + " order by voice_degree_fm desc,last_huanbo_time asc limit " + limitNums;
        return getWordsList(sql);
    }

    public List<OneWord> getAllWordListForHuanboOfDegree(int degree, int limitNums) {
        String sql = null;
        switch (degree) {
            case OneWord.DEGREE_UNFAMILIAR:
                sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where" + " degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and sense_degree_fm>"
                        + OneWord.SENSE_DEGREE_UNLEARN + " order by voice_degree_fm desc,last_huanbo_time asc";
                break;
            case OneWord.DEGREE_FAMILIAR:
                sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where" + " degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                        + OneWord.SENSE_DEGREE_FAMILIAR + " order by voice_degree_fm desc,last_huanbo_time asc";
                break;
            case OneWord.SENSE_DEGREE_TOO_EASY:
                sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where" + " degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm="
                        + OneWord.SENSE_DEGREE_TOO_EASY + " order by voice_degree_fm desc,last_huanbo_time asc";
                break;
        }
        sql = sql + " limit " + limitNums;
        return getWordsList(sql);
    }

    // 得到新学单词或最近忘记的单词
    public int getNumberOfNewWordOrLastForgetWords() {
        String sql = "select count(1) from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR
                + " and (sense_degree_fm/10=1 or sense_degree_fm/10=2" + " or time_forget > " + (DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() - 24 * 60) + ")";
        return getInt(sql);
    }

    // 得到包含一个词根词缀的所有单词
    public List<OneWord> getWordListOfAffix(int aff_id) {
        String sql = "select " + TABLE_WORDS + ".* from " + TABLE_WORDS + "," + TABLE_AFFIX_NODE + " where " + TABLE_WORDS + ".index_word_id=" + TABLE_AFFIX_NODE + ".index_word_id" + " and "
                + TABLE_AFFIX_NODE + ".aff_id=" + aff_id + " order by " + TABLE_WORDS + ".level_min asc";
        return getWordsList(sql, true);
    }

    // 得到包含一个词根词缀的所有单词,index_word_id在最前
    public List<OneWord> getWordListOfAffix(int aff_id, int index_word_id) {
        List<OneWord> list = new ArrayList<OneWord>();
        // list.add(getOneWordById(index_word_id)); //暂时不包含本单词
        String sql = "select " + TABLE_WORDS + ".* from " + TABLE_WORDS + "," + TABLE_AFFIX_NODE + " where " + TABLE_WORDS + ".index_word_id=" + TABLE_AFFIX_NODE + ".index_word_id" + " and "
                + TABLE_AFFIX_NODE + ".index_word_id!=" + index_word_id + " and " + TABLE_AFFIX_NODE + ".aff_id=" + aff_id + " order by " + TABLE_WORDS + ".level_min asc";
        list.addAll(getWordsList(sql, true));
        return list;
    }

    // 判断某一单词是否属于当前单词书
    public boolean isContainInRecentBook(int index_word_id) {
        StringBuilder where1 = new StringBuilder();

        if (StudyPlan.isLearningWordBook(context)) {
            where1.append("book_id=");
            where1.append(AppPreference.Book.BOOK_ID[AppPreference.getWordBookIndex(context)]);
        }

        if (StudyPlan.isLearningPhraseBook(context)) {
            if (where1.length() > 0) {
                where1.append(" or ");
            }
            where1.append("book_id=");
            where1.append(AppPreference.Book.BOOK_ID[AppPreference.getPhraseBookIndex(context)]);
        }

        String sql = "select count(1) from " + TABLE_WORDS_LEVELS + " where (" + where1.toString() + ") and index_word_id=" + index_word_id;
        return getInt(sql) > 0;
    }

    /**
     * **************** insert *****************
     */

    // 根据学习计划每天将单词加入生词表，作为准备学习单词 degree_fm=10, sense_degree_fm=0
    // 这里有个情况：如果是配套单词书，里面含有单词和词组，都当做单词看待
    public void addToUnFamiliarWordList(int num) {
        int wordPerNum = DateTimeUtils.getWordBookNumPerDayForPlan(context, false); // 每天学习的单词个数
        int phrasePerNum = DateTimeUtils.getPhraseBookNumPerDayForPlan(context, false); // 每天学习的词组个数
        int allNum = wordPerNum + phrasePerNum; // 总个数

        int wordCount = wordPerNum * num / allNum + (wordPerNum % allNum >= 0.5 ? 1 : 0); // 按照比例来设定添加单词和词组的个数
        int phraseCount = num - wordCount;

        LogUtil.d("加学新词,wordCount=" + wordCount + ",phraseCount=" + phraseCount);

        String sql = "select index_word_id from " + TABLE_WORDS_LOG;
        String sql2 = null;
        String sql3 = null;

        if (StudyPlan.isLearningWordBook(context)) {
            LogUtil.d("添加单词");
            sql2 = "select " + "index_word_id," + OneWord.DEGREE_UNFAMILIAR + "," + DateTimeUtils.getDate() + " from " + TABLE_WORDS_LEVELS + " where book_id="
                    + AppPreference.Book.BOOK_ID[AppPreference.getWordBookIndex(context)] + " and index_word_id not in (" + sql + ")" + " order by ind asc limit " + wordCount;

            sql3 = "insert into " + TABLE_WORDS_LOG + " (index_word_id,degree_fm,time_set) " + sql2;
            dbexe(sql3);
        }

        if (StudyPlan.isLearningPhraseBook(context)) {
            LogUtil.d("添加词组");
            sql2 = "select " + "index_word_id," + OneWord.DEGREE_UNFAMILIAR + "," + DateTimeUtils.getDate() + " from " + TABLE_WORDS_LEVELS + " where book_id="
                    + AppPreference.Book.BOOK_ID[AppPreference.getPhraseBookIndex(context)] + " and index_word_id not in (" + sql + ")" + " order by ind asc limit " + phraseCount;
            sql3 = "insert into " + TABLE_WORDS_LOG + " (index_word_id,degree_fm,time_set) " + sql2;
            dbexe(sql3);
        }

    }

    // 根据学习计划每天将单词加入生词表，作为准备学习单词 degree_fm=10, sense_degree_fm=0
    public void addToUnFamiliarWordList(int num, int bookIndex) {
        String sql = "select index_word_id from " + TABLE_WORDS_LOG;
        String sql2 = "select " + "index_word_id," + OneWord.DEGREE_UNFAMILIAR + "," + DateTimeUtils.getDate() + " from " + TABLE_WORDS_LEVELS + " where book_id="
                + AppPreference.Book.BOOK_ID[bookIndex] + " and index_word_id not in (" + sql + ")" + " order by ind asc limit " + num;
        String sql3 = "insert into " + TABLE_WORDS_LOG + " (index_word_id,degree_fm,time_set) " + sql2;
        dbexe(sql3);
    }

    /**
     * **************** delete *****************
     */

    // 从等待学习单词列表删除单词 注释 by ct v2.11.0
    // public void deleteFromUnFamiliarWordList(int num) {
    // String sql = "select " + TABLE_WORDS_LOG + ".index_word_id from " +
    // TABLE_WORDS_LOG + " inner join " + TABLE_WORDS_LEVELS + " on " +
    // TABLE_WORDS_LOG + ".index_word_id=" + TABLE_WORDS_LEVELS
    // + ".index_word_id" + " and " + TABLE_WORDS_LEVELS + ".book_id=" +
    // AppPreference.Book.BOOK_ID[AppPreference.getBook(context)] +
    // " where degree_fm=" + OneWord.DEGREE_UNFAMILIAR
    // + " and sense_degree_fm=0" + " order by ind desc limit " + num;
    // String sql1 = "delete from " + TABLE_WORDS_LOG +
    // " where index_word_id in (" + sql + ")";
    // dbexe(sql1);
    // }

    // 从等待学习单词列表删除非当前单词本中的单词，移至未学列表
    public void deleteFromUnFamiliarWordList() {
        String sql = "delete from " + TABLE_WORDS_LOG + " where sense_degree_fm=0" + " and degree_fm=" + OneWord.DEGREE_UNFAMILIAR;
        dbexe(sql);
    }

    // 从words_log删除单词
    public void deleteFromWordsLog(int word_id) {
        String sql = "delete from " + TABLE_WORDS_LOG + " where index_word_id=" + word_id;
        dbexe(sql);
    }

    /**
     * ******* update ***********************
     */

    public void markWordDegree(int wordId, int familiarDegree) {
        String sql = "update " + TABLE_WORDS_LOG + " set degree_fm=" + familiarDegree + ",time_set=" + DateTimeUtils.getDate() + " where index_word_id=" + wordId;
        dbexe(sql);
    }

    // 修改熟悉度和学习时间
    public void updateCurrentWordDegree(OneWord word) {
        String sql = "update " + TABLE_WORDS_LOG + " set sense_degree_fm=" + word.getSense_degree_fm() + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    // 修改巩固次数
    public void updateConsolidateTestCounts(OneWord word) {
        String sql = "update " + TABLE_WORDS_LOG + " set test_count=" + word.getConsolidateTestCount() + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    // 修改巩固熟词下次学习时间
    public void updateConsolidateNextTime(OneWord word) {
        String sql = "update " + TABLE_WORDS_LOG + " set next_test_time=" + word.getConsolidateNextTime() + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    // 修改学习时间
    public void updateCurrentWordTestTime(OneWord word) {
        String sql = "update " + TABLE_WORDS_LOG + " set last_test_time=" + DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    // 初始化巩固熟词的值，专为初始化用户原始积累的熟词
    public void initAllConsolidateWords(int days) {
        String sql = "update " + TABLE_WORDS_LOG + " set time_set=" + DateTimeUtils.getDate() + ",last_test_time="
                + (DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() + (-4 * DateTimeUtils.MINUTES_OF_DAY)) + ",next_test_time="
                + ((DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() + ((days - 4) * DateTimeUtils.MINUTES_OF_DAY))) + " where next_test_time=0 and degree_fm=" + OneWord.DEGREE_FAMILIAR
                + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR;
        dbexe(sql);
    }

    // 初始化巩固熟词的值,从主流程中转化的熟词，在初始化的同时初始化熟悉度等
    public void initConsolidateWord(OneWord word, int days) {
        String sql = "update " + TABLE_WORDS_LOG + " set degree_fm=" + OneWord.DEGREE_FAMILIAR + ",sense_degree_fm=" + OneWord.SENSE_DEGREE_FAMILIAR + ",time_set=" + DateTimeUtils.getDate()
                + ",last_test_time=" + DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() + ",next_test_time="
                + (DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() + (days * DateTimeUtils.MINUTES_OF_DAY)) + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    // 修改忘记时间
    public void markCurrentForgetWord(OneWord word) {
        String sql = "update " + TABLE_WORDS_LOG + " set time_forget=" + DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime() + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    public void markCurrentLockedWord(OneWord word) {
        // 修改熟悉度
        String sql = "update " + TABLE_WORDS_LOG + " set time_lock=" + DateTimeUtils.getTimeOfSecondWithZoneRawOffsetTime() + " where index_word_id=" + word.getIndex_word_id();
        dbexe(sql);
    }

    //
    public void updateWordSenseFamiliarDegree(OneWord word) {
        int difficulty;
        if (word.isDifficulty())
            difficulty = 1;
        else
            difficulty = 0;
        String sql = "replace into " + TABLE_WORDS_LOG + "(index_word_id,sense_degree_fm,difficulty) values " + "(" + word.getIndex_word_id() + "," + word.getSense_degree_fm() + "," + difficulty
                + ")";
        dbexe(sql);
    }

    //
    public void markCurrentHuanboWord(OneWord word) {
        // 修改熟悉度
        String sql = "update " + TABLE_WORDS_LOG + " set voice_degree_fm=" + word.getVoice_degree_fm() + ",last_huanbo_time=" + word.getLast_huanbo_time() + " where index_word_id="
                + word.getIndex_word_id();
        dbexe(sql);
    }

    /**
     * ************* RelationShip ****************************
     */

    public List<RelationShip> getRelationshipOfWord(int index_word_id, int num) {
        List<RelationShip> list = new ArrayList<RelationShip>(); // 原为LinkedList
        String sql = "select * from " + TABLE_COLLOC + " where index_word_id=" + index_word_id + " order by node_index asc limit " + num;
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            list.add(new RelationShip(c));
        }
        c.close();
        return list;
    }

    public RelationShip getRelationshipOfWordForTest(int index_word_id, int node_index) {
        RelationShip r = null;
        String sql = "select * from " + TABLE_COLLOC + " where index_word_id=? and node_index=?";
        Cursor c = db.rawQuery(sql, new String[]{index_word_id + "", node_index + ""});
        while (c.moveToNext()) {
            if (c.getInt(6) > 0)
                r = new RelationShip(c, getSentence(c.getInt(6)));
            else
                r = new RelationShip(c);
        }
        c.close();
        return r;
    }

    // 得到某个单词的搭配数
    public int getNumOfRelationshipOfWord(int index_word_id) {
        int num = 0;
        String sql = "select count(1) from " + TABLE_COLLOC + " where index_word_id=?";
        Cursor c = db.rawQuery(sql, new String[]{index_word_id + ""});
        while (c.moveToNext()) {
            num = c.getInt(0);
        }
        c.close();
        return num;
    }

    /**
     * ******************* affix ****************
     */

    public Affix getOneAffixOfWhere(String where) {
        String sql = "select * from " + TABLE_AFFIX_INFO + " where " + where;
        Cursor c = db.rawQuery(sql, null);
        Affix aff = null;
        if (c.moveToNext())
            aff = new Affix(c);
        c.close();
        return aff;
    }

    /**
     * 获取词根词缀
     *
     * @param word_id
     * @return
     */
    public Affix getAffix(int affix_id) {
        Affix affix = null;
        Cursor c = db.query(TABLE_AFFIX_INFO, null, "aff_id=" + affix_id, null, null, null, null);
        if (c.moveToFirst()) {
            affix = new Affix(c);
        }
        c.close();
        return affix;
    }

    /**
     * 获取一个单词的词根词缀列表
     *
     * @param word_id
     * @return
     */
    public List<Affix> getAffixListOfOneWord(int word_id) {
        Cursor cursor;
        List<Affix> list = new ArrayList<Affix>();

        cursor = db.query(TABLE_AFFIX_NODE, null, "index_word_id=" + word_id, null, null, null, "seq_no");
        while (cursor.moveToNext()) {
            Cursor c = db.query(TABLE_AFFIX_INFO, null, "aff_id=" + cursor.getInt(2), null, null, null, null);
            if (c.moveToFirst()) {
                Affix affix = new Affix(c);
                list.add(affix);
            }
            c.close();
        }
        cursor.close();
        return list;
    }

    public Affix getWordRoot(int word_id) {

        Cursor cursor;
        Affix affix = null;

        cursor = db.query(TABLE_AFFIX_NODE, null, "index_word_id=" + word_id, null, null, null, "seq_no");
        while (cursor.moveToNext()) {
            Cursor c = db.query(TABLE_AFFIX_INFO, null, "aff_id=" + cursor.getInt(2), null, null, null, null);
            if (c.moveToFirst()) {
                if (c.getInt(4) == 2) {
                    affix = new Affix(c);
                    c.close();
                    break;
                }
            }
            c.close();
        }
        cursor.close();
        return affix;
    }

    public int getAffixNodeIndex(int word_id, int aff_id) {
        Cursor cursor;
        int index = -1;

        cursor = db.query(TABLE_AFFIX_NODE, null, "index_word_id=" + word_id + " and aff_id=" + aff_id, null, null, null, "ind");
        if (cursor.moveToNext()) {
            index = cursor.getInt(3);
        }
        cursor.close();
        return index;
    }

    /**
     * 近形词
     *
     * @param index_word_id
     * @param lemmaType     =1表示普通单词，2表示词组，3表示搭配
     * @param testMode
     * @return
     */
    public List<OneWord> getSameShape(int index_word_id, int lemmaType, boolean testMode) {

        Cursor c;
        List<OneWord> wordlist = new ArrayList<OneWord>();

        String sql = "select * from " + TABLE_SAME_SHAPE + " where index_word_id=" + index_word_id;
        c = db.rawQuery(sql, null);

        int level = 0;
        if (lemmaType == OneWord.LEMMA_TYPE_WORD && StudyPlan.isLearningWordBook(context)) { // 单词需要根据等级显示近形词，如果是词组，则全部显示
            level = getLevelByBookId(AppPreference.Book.BOOK_ID[AppPreference.getWordBookIndex(context)]);
        }

        if (c.moveToFirst()) {
            String lemma_same = c.getString(1);
            String lemmas[] = lemma_same.split(";");

            for (int i = 0; i < lemmas.length; i++) {
                // 学习流程中，最低等级大于单词书的等级的形近词，忽略
                if (lemmas[i].split(",").length == 1) {
                    continue;
                }
                if (lemmaType == OneWord.LEMMA_TYPE_WORD && testMode && Integer.parseInt(lemmas[i].split(",")[1]) > level)
                    continue;
                OneWord word = getOneWordById(Integer.parseInt(lemmas[i].split(",")[0]));
                if (word != null)
                    wordlist.add(word);
            }
            Collections.sort(wordlist, new SortByLevelMin());
        }

        c.close();

        return wordlist;
    }

    /**
     * ******* searchDate *******************
     */
    public OneWord getOneWordByLemma(String lemma) {
        Cursor c, c1;
        OneWord word = null;
        int book_id = 0;

        lemma = lemma.replace("'", "''");
        String sql = "select * from " + TABLE_WORDS + " left join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where lemma='" + lemma + "'";

        c = db.rawQuery(sql, null);

        if (c.moveToFirst()) {
            sql = "select * from " + TABLE_WORDS_LEVELS + " where index_word_id='" + c.getInt(0) + "' order by book_id asc";
            c1 = db.rawQuery(sql, null);
            while (c1.moveToNext()) {
                if (c1.getInt(c1.getColumnIndex("book_id")) == AppPreference.Book.BOOK_ID[6] || c1.getInt(c1.getColumnIndex("book_id")) == AppPreference.Book.BOOK_ID[0]
                        || c1.getInt(c1.getColumnIndex("book_id")) == AppPreference.Book.BOOK_ID[1] || c1.getInt(c1.getColumnIndex("book_id")) == AppPreference.Book.BOOK_ID[2])
                    book_id += c1.getInt(c1.getColumnIndex("book_id"));
                if (book_id == AppPreference.Book.BOOK_ID[6]) // 如果是高中单词，其他不需要计算
                    break;
            }

            word = new OneWord(c, book_id);
            c1.close();
        }

        c.close();
        return word;
    }

    public OneWord getOneWordByLemma1(String lemma) {
        Cursor c;
        OneWord word = null;
        String sql = "select * from " + TABLE_WORDS + " where lemma='" + lemma + "'";
        c = db.rawQuery(sql, null);
        if (c.moveToFirst()) {
            word = new OneWord(c, true);
        }
        c.close();
        return word;
    }

    public OneWord getOneWordByIndexWordID(int index_word_id, int degree) {
        Cursor c;
        OneWord word = null;

        String sql = "select * from " + TABLE_WORDS + " left join " + TABLE_WORDS_LOG + " using (index_word_id)" + " where index_word_id=" + index_word_id;

        switch (degree) {
            case OneWord.DEGREE_UNFAMILIAR:
                sql = sql + " and degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and sense_degree_fm>0 ";
                break;
            case OneWord.DEGREE_FAMILIAR:
                sql = sql + " and degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm<" + OneWord.SENSE_DEGREE_TOO_EASY;
                break;
            case OneWord.SENSE_DEGREE_TOO_EASY:
                sql = sql + " and degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_TOO_EASY;
                break;
            case OneWord.SENSE_DEGREE_TOO_EASY_FROM_CONSOLIDATE:
                sql = sql + " and degree_fm=" + OneWord.DEGREE_FAMILIAR + " and sense_degree_fm=" + OneWord.SENSE_DEGREE_TOO_EASY;
                break;
            default:
                return null;
        }

        c = db.rawQuery(sql, null);

        if (c.moveToNext())
            word = new OneWord(c);
        c.close();
        return word;
    }

    // 搜索单词，按lemma排序
    public List<searchDate> getSearchData(String in) {
        List<searchDate> searchs = new ArrayList<searchDate>();
        long start = System.currentTimeMillis();
        in = in.replace("'", "''");
        String in1 = null;
        if (in.length() >= 2 && in.substring(in.length() - 2).equals("''"))
            in1 = in.substring(0, in.length() - 2) + '(';
        else
            in1 = StringUtil.nextString(in);
        in1 = in1.replace("'", "''");
        String sql = "select lemma,senses_senior,phonetic from " + TABLE_WORDS + " where lemma>='" + in + "'" + " and lemma<'" + in1 + "'" + " order by lemma limit 10";
        // + " where lemma like '" + in + "%'" + " limit 10";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            String lemma = cursor.getString(0);
            String senses = cursor.getString(1).replace("#n", "  ");
            String phonetic = cursor.getString(2);
            searchs.add(new searchDate(lemma, senses, phonetic));
        }
        cursor.close();

        long end = System.currentTimeMillis();
        Log.e("search", in + (end - start));

        return searchs;
    }

    /**
     * ******* Sentence *******************
     */
    public Sentence getSentence(int exaSentenceId) {
        Cursor cursor;
        cursor = db.query(TABLE_EXA_SENTENCE, null, "exa_id=" + exaSentenceId, null, null, null, null);
        Sentence sentence = null;
        if (cursor.moveToFirst()) {
            sentence = new Sentence(cursor.getInt(0), cursor.getString(1), cursor.getString(2));
        }
        cursor.close();
        return sentence;
    }

    /**
     * ******** order *************************************
     */

    public static int rand(int total) {
        return ((int) (Math.pow(Math.random(), (1.0 + MAGICRATE)) * total) % total);
    }

    public String getStringBySQL(String sql) {
        Cursor c = db.rawQuery(sql, null);

        String ret = null;
        if (c.moveToNext()) {
            ret = c.getString(0);
        }
        c.close();
        return ret;
    }

    // 创建索引
    public void createIndexs() {
        String sqls[] = {"CREATE UNIQUE INDEX [IDX_COLLOC_] ON [colloc]" + "([index_word_id]  DESC,[node_id]  DESC)", "CREATE UNIQUE INDEX [IDX_WORDS_] ON [words]([lemma]  DESC)",
                "CREATE INDEX [IDX_WORDS_TYPE] ON [words]([lemma_type]  DESC)", "CREATE INDEX [IDX_WORDS_LEVELS_] ON [words_levels]" + "([book_id]  DESC,[ind]  DESC)",
                "CREATE UNIQUE INDEX [IDX_AFFIX_NODE_] ON [affix_node]" + "([index_word_id]  DESC,[aff_id]  DESC)",
                "CREATE INDEX [IDX_WORDS_LOG_] ON [words_log]" + "([degree_fm]  DESC,[sense_degree_fm]  DESC,[last_test_time]  DESC)",
                "CREATE UNIQUE INDEX [IDX_PUNCH_OUT_LOG_] ON [punch_out_log]([date]  DESC)", "CREATE INDEX [IDX_SAME_SHAPE_] ON [sameshape]([index_word_id] DESC)"};
        try {
            for (int i = 0; i < sqls.length; i++) {
                db.execSQL(sqls[i]);
            }

        } catch (Exception e) {
            e.printStackTrace();
            Log.e("create index", "error");
        }
        Log.e("index", "创建索引完成");
    }

    public int getWordsLogCount() {
        String sql = "select count(1) from words_log where degree_fm>0 and sense_degree_fm>0";
        return getInt(sql);
    }

    // 获取单词Log列表用于同步
    public List<WordLog> getWordsLog() {
        WordLog wl;
        List<WordLog> wordsLog = new ArrayList<WordLog>();
        Cursor c;
        String sql = "select * from words_log where degree_fm>0 and sense_degree_fm>0";

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            wl = new WordLog(c.getInt(c.getColumnIndex("index_word_id")), c.getInt(c.getColumnIndex("time_set")), c.getInt(c.getColumnIndex("degree_fm")),
                    c.getInt(c.getColumnIndex("voice_degree_fm")), c.getInt(c.getColumnIndex("last_huanbo_time")), c.getInt(c.getColumnIndex("sense_degree_fm")), c.getInt(c
                    .getColumnIndex("last_test_time")), c.getInt(c.getColumnIndex("time_forget")), c.getInt(c.getColumnIndex("time_lock")), c.getInt(c.getColumnIndex("next_test_time")),
                    c.getInt(c.getColumnIndex("test_count")), c.getInt(c.getColumnIndex("difficulty")));
            wordsLog.add(wl);
        }
        c.close();
        return wordsLog;
    }

    // 删除Words_Log表中的数据
    public void deleteTableWordsLog() {
        try {
            String sql1 = "delete from words_log";
            db.execSQL(sql1);
        } catch (Exception e) {

        }
    }

    // 导入Words_Log
    public void importTableWordsLog(List<WordLog> wordsLog) {
        if (wordsLog == null)
            return;

        // String sql2 = "insert into " + TABLE_PUNCH_OUT_LOG +
        // "(date, time) values(" + date + "," + time + ")";

        db.beginTransaction();

        try {
            String sql1 = "delete from words_log";
            db.execSQL(sql1);

            for (int i = 0; i < wordsLog.size(); i++) {
                WordLog wl = wordsLog.get(i);
                String sql = "insert into words_log (index_word_id,time_set,degree_fm,voice_degree_fm,last_huanbo_time,sense_degree_fm,last_test_time,time_forget,time_lock,next_test_time,test_count) values ";
                sql = sql + "(" + wl.getIndex_word_id() + "," + wl.getTime_set() + "," + wl.getDegree_fm() + "," + wl.getVoice_degree_fm() + "," + wl.getLast_huanbo_time() + ","
                        + wl.getSense_degree_fm() + "," + wl.getLast_test_time() + "," + wl.getForget_time() + "," + wl.getLock_time() + "," + wl.getNext_test_time() + "," + wl.getTest_count() + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    // 导入Words_Log
    public void importTableWordsLog() {
        if (wordsLog == null) {
            MobclickAgentData.exception(context, "in WordFactory importTableWordsLog(): wordsLog null");
            wordsLog = getUserData();
            if (wordsLog == null) {
                MobclickAgentData.exception(context, "in WordFactory importTableWordsLog(): wordsLog null1");
                return;
            }
        }

        File file = new File(context.getFilesDir(), "temp");
        if (file.exists())
            file.delete();

        int oldDbVersion = AppPreference.getOldDbVersion(context);

        db.beginTransaction();

        try {
            String sql1 = "delete from words_log";
            db.execSQL(sql1);

            for (int i = 0; i < wordsLog.size(); i++) {
                WordLog wl = wordsLog.get(i);
                // 原数据库版本号<6，升级到6以上时，原生词和熟词的词义熟悉度需要*10
                if (oldDbVersion < 6)
                    wl.setSense_degree_fm(wl.getSense_degree_fm() * 10);
                String sql = "insert into words_log (index_word_id,time_set,degree_fm,voice_degree_fm,last_huanbo_time,sense_degree_fm,last_test_time,time_forget,time_lock,next_test_time,test_count) values ";
                sql = sql + "(" + wl.getIndex_word_id() + "," + wl.getTime_set() + "," + wl.getDegree_fm() + "," + wl.getVoice_degree_fm() + "," + wl.getLast_huanbo_time() + ","
                        + wl.getSense_degree_fm() + "," + wl.getLast_test_time() + "," + wl.getForget_time() + "," + wl.getLock_time() + "," + wl.getNext_test_time() + "," + wl.getTest_count() + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }

        wordsLog = null;
    }

    public static List<WordLog> wordsLog;

    public static void exportTableWordsLogFromWords(SQLiteDatabase db, Context context) {
        WordLog wl;
        wordsLog = new ArrayList<WordLog>();

        Cursor c;
        String sql = "select * from words where degree_fm>0";

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {

            wl = new WordLog(c.getInt(c.getColumnIndex("index_word_id")), c.getInt(c.getColumnIndex("time_set")), c.getInt(c.getColumnIndex("degree_fm")),
                    c.getInt(c.getColumnIndex("voice_degree_fm")), c.getInt(c.getColumnIndex("last_huanbo_time")), c.getInt(c.getColumnIndex("sense_degree_fm")), c.getInt(c
                    .getColumnIndex("last_test_time")), c.getInt(c.getColumnIndex("time_forget")), 0, 0, 0, 0);

            wordsLog.add(wl);
        }
        c.close();

        createUserData(context);
    }

    // 导出Words_Log,如果添加了新字段，要在这里进行版本判断
    public static void exportTableWordsLog(SQLiteDatabase db, int oldVersion, Context context) {

        WordLog wl;
        wordsLog = new ArrayList<WordLog>();

        Cursor c;
        String sql = "select * from words_log where degree_fm>0";

        c = db.rawQuery(sql, null);

        if (oldVersion <= 13) { // 添加了"next_test_time","test_count"
            while (c.moveToNext()) {
                wl = new WordLog(c.getInt(c.getColumnIndex("index_word_id")), c.getInt(c.getColumnIndex("time_set")), c.getInt(c.getColumnIndex("degree_fm")), c.getInt(c
                        .getColumnIndex("voice_degree_fm")), c.getInt(c.getColumnIndex("last_huanbo_time")), c.getInt(c.getColumnIndex("sense_degree_fm")),
                        c.getInt(c.getColumnIndex("last_test_time")), c.getInt(c.getColumnIndex("time_forget")), c.getInt(c.getColumnIndex("time_lock")), 0, 0, 0);

                wordsLog.add(wl);
            }
        } else if (oldVersion <= 17) { // v2.11.0中添加字段“difficulty”
            while (c.moveToNext()) {

                wl = new WordLog(c.getInt(c.getColumnIndex("index_word_id")), c.getInt(c.getColumnIndex("time_set")), c.getInt(c.getColumnIndex("degree_fm")), c.getInt(c
                        .getColumnIndex("voice_degree_fm")), c.getInt(c.getColumnIndex("last_huanbo_time")), c.getInt(c.getColumnIndex("sense_degree_fm")),
                        c.getInt(c.getColumnIndex("last_test_time")), c.getInt(c.getColumnIndex("time_forget")), c.getInt(c.getColumnIndex("time_lock")), c.getInt(c.getColumnIndex("next_test_time")),
                        c.getInt(c.getColumnIndex("test_count")), 0);

                wordsLog.add(wl);
            }
        } else {
            while (c.moveToNext()) {

                wl = new WordLog(c.getInt(c.getColumnIndex("index_word_id")), c.getInt(c.getColumnIndex("time_set")), c.getInt(c.getColumnIndex("degree_fm")), c.getInt(c
                        .getColumnIndex("voice_degree_fm")), c.getInt(c.getColumnIndex("last_huanbo_time")), c.getInt(c.getColumnIndex("sense_degree_fm")),
                        c.getInt(c.getColumnIndex("last_test_time")), c.getInt(c.getColumnIndex("time_forget")), c.getInt(c.getColumnIndex("time_lock")), c.getInt(c.getColumnIndex("next_test_time")),
                        c.getInt(c.getColumnIndex("test_count")), c.getInt(c.getColumnIndex("difficulty")));

                wordsLog.add(wl);
            }
        }

        c.close();

        createUserData(context);

    }

    // 加一个保险，升级时，将用户数据写入文件
    static void createUserData(Context context) {
        SynData synData = SynData.createSynData(context, wordsLog);
        byte b[] = null;
        String data = null;
        try {
            b = ThriftSerialization.serialize(synData);
            data = StringZip.compressFromByteArray(b);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (TException e) {
            e.printStackTrace();
        }
        if (data != null) {
            File file = new File(context.getFilesDir(), "temp");
            try {
                // 打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件
                FileWriter writer = new FileWriter(file, false);
                writer.write(data);
                writer.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    // 将用户的数据从文件读出
    List<WordLog> getUserData() {
        File file = new File(context.getFilesDir(), "temp");
        if (!file.exists())
            return null;

        String read;
        String data = "";
        FileReader fileread;
        BufferedReader bufread;
        try {
            fileread = new FileReader(file);
            bufread = new BufferedReader(fileread);
            try {
                char buf[] = new char[1024];
                int len;
                while ((len = bufread.read(buf)) != -1) {
                    data = data + new String(buf, 0, len);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        SynData userSynData = null;
        byte b[];
        try {
            b = StringZip.unCompressToByteArray(data);
            userSynData = ThriftSerialization.deserialize(b);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (TException e) {
            e.printStackTrace();
        }

        file.delete();

        return userSynData.getUserData();
    }

    // index_word_id<=2000 996单词
    // index_word_id<=200000 5213单词
    // index_word_id<=1102000 8670单词
    // index_word_id<=1133256 31854单词
    public void updateWordForTest() {
        String sql2 = "select " + "index_word_id,10,10" + " from " + TABLE_WORDS + " where index_word_id<=1100";
        String sql3 = "replace into " + TABLE_WORDS_LOG + " (index_word_id,degree_fm,sense_degree_fm) " + sql2;
        dbexe(sql3);
    }

    /* punchout */
    public void insertPunchOutLog() {
        int year = DateTimeUtils.getYear();
        int month = DateTimeUtils.getMonth();
        int day = DateTimeUtils.getCurrentMonthDay();
        int date = year % 100 * 10000 + month * 100 + day;
        if (!todayHasPunchOut()) {
            String sql2 = "insert into " + TABLE_PUNCH_OUT_LOG + "(date) values(" + date + ")";
            db.execSQL(sql2);
        }
    }

    public void insertPunchOutLog(int date) {
        String sql2 = "insert into " + TABLE_PUNCH_OUT_LOG + "(date) values(" + date + ")";
        db.execSQL(sql2);
    }

    public void insertPunchOutLog(int date, int time) {
        String sql2 = "insert into " + TABLE_PUNCH_OUT_LOG + "(date, time) values(" + date + "," + time + ")";
        db.execSQL(sql2);
    }

    /**
     * 今天是否已经打过卡
     *
     * @return false表示还没有
     */
    public boolean todayHasPunchOut() {
        int year = DateTimeUtils.getYear();
        int month = DateTimeUtils.getMonth();
        int day = DateTimeUtils.getCurrentMonthDay();
        int date = year % 100 * 10000 + month * 100 + day;
        String sql1 = "select count(*) from " + TABLE_PUNCH_OUT_LOG + " where date=" + date;
        int count = getInt(sql1);
        if (count == 0) {
            return false;
        } else {
            return true;
        }
    }

    public int getPunchOutTimes() {
        String sql = "select count(*) from " + TABLE_PUNCH_OUT_LOG;
        int times = getInt(sql);
        return times;
    }

    // 删除打卡记录表中的数据
    public void deleteTablePunchLog() {
        try {
            String sql1 = "delete from " + TABLE_PUNCH_OUT_LOG;
            db.execSQL(sql1);
        } catch (Exception e) {

        }
    }

    public List<Integer> getDaysByYearAndMonth(int year, int month) {
        int yearMonth = year % 100 * 100 + month;
        String sql = "select date from " + TABLE_PUNCH_OUT_LOG + " where date/100=" + yearMonth;
        Cursor c;
        List<Integer> days = new ArrayList<Integer>();

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            days.add(c.getInt(0) % 100);
        }
        c.close();
        return days;
    }

    // 导入TABLE_PUNCH_OUT_LOG
    public void importTablePunchOutLog(String dateList[]) {
        if (dateList == null)
            return;

        db.beginTransaction();

        try {
            for (int i = 0; i < dateList.length; i++) {
                String sql = "insert into " + TABLE_PUNCH_OUT_LOG + "(date) values(" + dateList[i].split("T")[0] + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    public static ArrayList<Integer> punchOutLog;

    // 导出punch_out_log
    public static void exportTablePunchOutLog(SQLiteDatabase db) {
        LogUtil.i("Tag", "导出打卡表");
        punchOutLog = new ArrayList<Integer>();

        Cursor c;
        String sql = "select date from punch_out_log";

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            punchOutLog.add(c.getInt(0));
        }
        c.close();

    }

    // 导入punch_out_log
    public void importTablePunchOutLog() {
        LogUtil.d("Tag", "恢复打卡记录,punchOutLog=" + punchOutLog);
        if (punchOutLog == null)
            return;

        db.beginTransaction();

        try {
            String sql1 = "delete from punch_out_log";
            db.execSQL(sql1);

            for (int i = 0; i < punchOutLog.size(); i++) {
                String sql = "insert into " + TABLE_PUNCH_OUT_LOG + "(date) values(" + punchOutLog.get(i) + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }

        punchOutLog = null;
    }

    /*----------------- product ---------------------*/
    public List<Product> getProducts(int productType) {
        List<Product> productList = new ArrayList<Product>();
        Cursor c;
        String sql = "select * from " + TABLE_PRODUCT + " where product_type=" + productType + " order by price asc";

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            productList.add(new Product(c));
        }
        c.close();
        return productList;
    }

    public Product getProduct(int productID) {
        Product product = null;
        Cursor c;
        String sql = "select * from " + TABLE_PRODUCT + " where id=" + productID;

        c = db.rawQuery(sql, null);
        if (c.moveToNext()) {
            product = new Product(c);
        }
        c.close();
        return product;
    }

    public int getPrice(int book_index) {

        if (book_index == 20 || book_index == 21) {// 中考必备单词与词组 高考必备单词与词组
            book_index -= 2;
        } else if (book_index == 102) { // 四级单词+词组
            book_index = 101;
        } else if (book_index == 107) { // 六级单词+词组
            book_index = 106;
        } else if (book_index == 105) { // 考研单词+词组
            book_index = 103;
        } else if (book_index == 109) { // 雅思单词+词组
            book_index = 108;
        } else if (book_index == 111) { // 托福单词+词组
            book_index = 110;
        }

        int price = 0;
        Cursor c;
        String sql = "select price from " + TABLE_PRODUCT + " where id=" + book_index + " and product_type=1";

        c = db.rawQuery(sql, null);
        if (c.moveToNext()) {
            price = c.getInt(0);
        }
        c.close();
        return price;
    }

    public void importProductList(List<Product> productList) {
        if (productList == null)
            return;

        db.beginTransaction();

        try {
            for (int i = 0; i < productList.size(); i++) {
                Product product = productList.get(i);
                String sql = "replace into " + TABLE_PRODUCT + "(product_name,product_type,price,id) values " + "(" + "'" + product.getProductName() + "'," + product.getProductType() + ","
                        + product.getPrice() + "," + product.getId() + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }

    }

    /*----------------- order ---------------------- */
    public Order getOrderById(long orderID) {
        Order order = null;
        Cursor c;
        String sql = "select * from " + TABLE_ORDER_LOG + " where orderId=" + orderID;

        c = db.rawQuery(sql, null);
        if (c.moveToNext()) {
            order = new Order(c);
        }
        c.close();
        return order;
    }

    public List<Order> getOrdersLog() {
        List<Order> ordersLog = new ArrayList<Order>();
        Cursor c;
        String sql = "select * from " + TABLE_ORDER_LOG + " order by gmtModified desc";

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            ordersLog.add(new Order(c));
        }
        c.close();
        return ordersLog;
    }

    public void insertOrderLog(Order order) {
        String sql = "insert into order_log (orderId,userId,ProductId,tradeStatus,orderAmount,orderTime,gmtCreate,gmtModified) values ";
        sql = sql + "(" + order.getOrderId() + "," + order.getUserId() + "," + order.getProductId() + "," + order.getTradeStatus() + "," + order.getOrderAmount() + "," + order.getOrderTime() + ","
                + order.getGmtCreate() + "," + order.getGmtModified() + ")";
        db.execSQL(sql);
    }

    // 是否已购买
    public boolean isBought(int bookID) {
        if (bookID == 20 || bookID == 21) {
            bookID -= 2;
        } else if (bookID == 102) { // 四级单词+词组
            bookID = 101;
        } else if (bookID == 107) { // 六级单词+词组
            bookID = 106;
        } else if (bookID == 105) { // 考研单词+词组
            bookID = 103;
        } else if (bookID == 109) { // 雅思单词+词组
            bookID = 108;
        } else if (bookID == 111) { // 托福单词+词组
            bookID = 110;
        }

        String sql1 = "select count(1) from " + TABLE_ORDER_LOG + " where productId=" + bookID + " and tradeStatus=1";
        return getInt(sql1) > 0;
    }

    // 删除某一笔交易记录
    public void deleteOrderLog(long orderID) {
        try {
            String sql1 = "delete from " + TABLE_ORDER_LOG + " where orderId=" + orderID;
            db.execSQL(sql1);
        } catch (Exception e) {

        }
    }

    // 删除最近一笔未成功的交易
    public void deleteLastOrderLog() {
        try {
            String sql1 = "delete from " + TABLE_ORDER_LOG + " where tradeStatus=0 and gmtCreate=(select max(gmtCreate) from " + TABLE_ORDER_LOG + ");";
            db.execSQL(sql1);
        } catch (Exception e) {

        }
    }

    // 删除交易记录表中的数据
    public void deleteTableOrderLog() {
        try {
            String sql1 = "delete from " + TABLE_ORDER_LOG;
            db.execSQL(sql1);
        } catch (Exception e) {

        }
    }

    public void importOrderList(List<Order> orderList) {
        if (orderList == null)
            return;

        db.beginTransaction();

        try {
            for (int i = 0; i < orderList.size(); i++) {
                Order order = orderList.get(i);
                order.setTradeStatus(1);
                String sql = "replace into " + TABLE_ORDER_LOG + "(orderId,userId,productId,tradeStatus,gmtCreate,gmtModified) values " + "(" + order.getOrderId() + "," + order.getUserId() + ","
                        + order.getProductId() + "," + order.getTradeStatus() + "," + order.getGmtCreate() + "," + order.getGmtModified() + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }

        // LogUtil.addLog("导入punch_out_log完成");
    }

    public static List<Order> orderLog;

    public static void exportTableOrderLog(SQLiteDatabase db) {
        orderLog = new ArrayList<Order>();
        Cursor c;
        String sql = "select * from " + TABLE_ORDER_LOG + " order by gmtModified desc";

        c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            orderLog.add(new Order(c));
        }
        c.close();

    }

    public void importTableOrderLog() {
        if (orderLog == null)
            return;

        db.beginTransaction();

        try {
            for (int i = 0; i < orderLog.size(); i++) {
                Order order = orderLog.get(i);
                order.setTradeStatus(1);
                String sql = "replace into " + TABLE_ORDER_LOG + "(orderId,userId,productId,tradeStatus,gmtCreate,gmtModified) values " + "(" + order.getOrderId() + "," + order.getUserId() + ","
                        + order.getProductId() + "," + order.getTradeStatus() + "," + order.getGmtCreate() + "," + order.getGmtModified() + ")";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }

        orderLog = null;
    }

    /* --------------wealth------------- */
    public int getZMD() {
        return getInt("select zmd from " + TABLE_WEALTH);
    }

    public void setZMD(int zmd) {
        String sql = "update " + TABLE_WEALTH + " set zmd=" + zmd;
        dbexe(sql);
    }

    public static int zmd;

    // 导出zmd
    public static void exportTableZMD(SQLiteDatabase db) {
        zmd = 0;

        Cursor c;
        String sql = "select zmd from " + TABLE_WEALTH;

        c = db.rawQuery(sql, null);
        if (c.moveToNext()) {
            zmd = c.getInt(0);
        }
        c.close();

    }

    // 导入zmd
    public void importTableZMD() {
        if (zmd == 0)
            return;

        db.beginTransaction();

        try {
            String sql = "update " + TABLE_WEALTH + " set zmd=" + zmd;
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            db.endTransaction(); // 处理完成
        }

        zmd = 0;
    }

    public String getBookName(int book_id) {
        String sql = "select name from word_book where book_id=" + book_id;
        Cursor c = db.rawQuery(sql, null);
        String bookName = null;
        while (c.moveToNext()) {
            bookName = c.getString(0);
        }
        c.close();
        return bookName;
    }

    // 修正老版本升级后，sense_degree_fm*10产生的数据错误值
    public void updateUnfamilarWordSenseDegree() {
        String sql = "update " + TABLE_WORDS_LOG + " set sense_degree_fm=sense_degree_fm/10 where degree_fm=10 and sense_degree_fm>100";
        dbexe(sql);
        sql = "update " + TABLE_WORDS_LOG + " set sense_degree_fm=60 where sense_degree_fm=600";
        dbexe(sql);
        sql = "update " + TABLE_WORDS_LOG + " set sense_degree_fm=1000 where sense_degree_fm=10000";
        dbexe(sql);
    }

    public int getTempTimeSet(int wordID) {
        String sql = "select time_set from " + TABLE_WORDS_LOG + " where index_word_id=" + wordID;
        return getInt(sql);
    }

    public int getTempDegreeFm(int wordID) {
        String sql = "select degree_fm from " + TABLE_WORDS_LOG + " where index_word_id=" + wordID;
        return getInt(sql);
    }

    public int getTempSenseDegreeFm(int wordID) {
        String sql = "select sense_degree_fm from " + TABLE_WORDS_LOG + " where index_word_id=" + wordID;
        return getInt(sql);
    }

    public int getTempLastTestTime(int wordID) {
        String sql = "select last_test_time from " + TABLE_WORDS_LOG + " where index_word_id=" + wordID;
        return getInt(sql);
    }

    public int getTempTimeNext(int wordID) {
        String sql = "select next_test_time from " + TABLE_WORDS_LOG + " where index_word_id=" + wordID;
        return getInt(sql);
    }

    public int getTempTestCount(int wordID) {
        String sql = "select test_count from " + TABLE_WORDS_LOG + " where index_word_id=" + wordID;
        return getInt(sql);
    }

    public int getLevelByBookId(int bookId) {
        String sql = "select level from " + TABLE_WORD_BOOK + " where book_id=" + bookId;
        return getInt(sql);
    }

    // 通过index_word_id获得所在单词书
    public int getMinLevelBookIdByWordId(int index_word_id) {
        String sql = "select book_id from " + TABLE_WORDS_LEVELS + " where index_word_id=" + index_word_id + " order by book_id asc";
        int id = getInt(sql);
        return id;
    }

    public void fixDB() {
        LogUtil.e("检测数据库");
        Cursor c;
        String sql;
        boolean isToFix = false;

        sql = "select * from words_log where (test_count > 1000) or (sense_degree_fm >= 60 and sense_degree_fm < 1000 and last_test_time > next_test_time)";

        c = db.rawQuery(sql, null);
        if (c.getCount() > 0) {
            isToFix = true;
        }

        c.close();
        if (isToFix) {
            LogUtil.e("修复数据库");
            toFixDB();
        }
    }

    // 修复2.10.2 word_log表最后4列混乱
    private void toFixDB() {
        String sql = "select index_word_id,time_set,degree_fm,sense_degree_fm,last_test_time from words_log";
        Cursor c;
        c = db.rawQuery(sql, null);
        int set_next_test_time = 0;
        int set_test_count = 0;
        int set_time_forget = 0;
        int set_time_lock = 0;
        int index_word_id;
        int Tlast, Ti, degree_fm, sense_degree_fm;

        while (c.moveToNext()) {
            set_next_test_time = 0;
            set_test_count = 0;
            set_time_forget = 0;
            set_time_lock = 0;

            index_word_id = c.getInt(c.getColumnIndex("index_word_id"));
            Tlast = DateTimeUtils.getDateFromTimeOfMinuteWithZoneRawOffsetTime(c.getInt(c.getColumnIndex("last_test_time")));
            Ti = c.getInt(c.getColumnIndex("time_set"));
            degree_fm = c.getInt(c.getColumnIndex("degree_fm"));
            sense_degree_fm = c.getInt(c.getColumnIndex("sense_degree_fm"));
            if (degree_fm == 20 && sense_degree_fm == 60) {
                if (Tlast < Ti) {
                    set_next_test_time = Ti + 10;
                } else {
                    int i = log(((Tlast - Ti) + 7) / 7, 2);
                    set_test_count = i;
                    set_next_test_time = (int) (Tlast + 7 * Math.pow(2, i));
                }
            }
            try {
                DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime(set_next_test_time);
            } catch (Exception e) {
            }

            dbexe("update " + TABLE_WORDS_LOG + " set next_test_time=" + DateTimeUtils.getTimeOfMinuteWithZoneRawOffsetTime(set_next_test_time) + " , test_count=" + set_test_count + " , time_forget="
                    + set_time_forget + " , time_lock=" + set_time_lock + " where index_word_id=" + index_word_id);

        }
        c.close();

    }

    private int log(int x, int base) {
        return (int) (Math.log(x) / Math.log(base));
    }

    /**
     * 获取词组书中50个词组，并打乱
     *
     * @return
     */
    public ArrayList<OneWord> getTop50Words(int bookIndex) {
        String sql = "select * from " + TABLE_WORDS + " inner join " + TABLE_WORDS_LEVELS + " using (index_word_id) where book_id=" + AppPreference.Book.BOOK_ID[bookIndex]
                + " order by index_word_id desc limit 0,50";
        ArrayList<OneWord> top50Words = (ArrayList<OneWord>) getWordsList(sql, true);
        Collections.shuffle(top50Words);
        return top50Words;
    }

    /**
     * 在word_log表中随机删除一个单词，sense_degree_fm=0表示原来的未学单词
     */
    public void randomDeleteWord() {
        String sql = "delete from " + TABLE_WORDS_LOG + " where index_word_id in (select index_word_id from " + TABLE_WORDS_LOG + " inner join " + TABLE_WORDS + " using (index_word_id) where "
                + TABLE_WORDS + ".lemma_type=" + OneWord.LEMMA_TYPE_WORD + " and " + TABLE_WORDS_LOG + ".degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and " + TABLE_WORDS_LOG
                + ".sense_degree_fm=0 limit 1)";
        dbexe(sql);
    }

    /**
     * 在word_log表中随机删除一个词组,sense_degree_fm=0表示原来的未学词组
     */
    public void randomDeletePhrase() {
        String sql = "delete from " + TABLE_WORDS_LOG + " where index_word_id in (select index_word_id from " + TABLE_WORDS_LOG + " inner join " + TABLE_WORDS + " using (index_word_id) where "
                + TABLE_WORDS + ".lemma_type>" + OneWord.LEMMA_TYPE_WORD + " and " + TABLE_WORDS_LOG + ".degree_fm=" + OneWord.DEGREE_UNFAMILIAR + " and " + TABLE_WORDS_LOG
                + ".sense_degree_fm=0 limit 1)";
        dbexe(sql);
    }

    public boolean isContainerInWordLog(int index_word_id) {
        String sql = "select count(*) from " + TABLE_WORDS_LOG + " where index_word_id=" + index_word_id;
        return getInt(sql) > 0;
    }
}